﻿CREATE PROCEDURE [vantage].[s_Admin_CreateObjects_10] AS
EXEC Vantage_In.dbo.sp_executesql N'

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[t_Payment_Last]'') AND type in (N''U''))
DROP TABLE [dbo].[t_Payment_Last]

SELECT     AccountNumber, MAX(PaymentEffectiveDate) AS PaymentEffectiveDate
INTO            t_Payment_Last
FROM         Target_Payment
WHERE PaymentStatus = ''Payment Applied''
GROUP BY AccountNumber

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[t_Address_Current]'') AND type in (N''U''))
DROP TABLE [dbo].[t_Address_Current]

SELECT     AddressTypeRef, LoanClientID, MAX(EffectiveDate) AS EffectiveDate,MAX(Target_LoanClientAddressID)Target_LoanClientAddressID
INTO t_Address_Current
FROM         dbo.Target_LoanClientAddress
WHERE     (ExpiryDate = CONVERT(DATETIME, ''3000-01-01 00:00:00'', 102)) AND AddressTypeRef = ''Home''
GROUP BY AddressTypeRef, LoanClientID

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[t_ClientPhones]'') AND type in (N''U''))
DROP TABLE [dbo].[t_ClientPhones];

WITH PC AS (SELECT     CLIENT_1.LoanClientID, P.PhoneNumber, CLIENT_1.PhoneTypeRef
                             FROM         (SELECT     PhoneTypeRef, LoanClientID, MAX(Target_LoanClientPhoneID) AS Target_LoanClientPhoneID
                                                    FROM          dbo.Target_LoanClientPhone
                                                    WHERE (ExpiryDate = CONVERT(DATETIME, ''3000-01-01 00:00:00'', 102)) AND PhoneNumber <> ''0000000000''
                                                    GROUP BY PhoneTypeRef, LoanClientID) AS CLIENT_1 LEFT OUTER JOIN
                                                   dbo.Target_LoanClientPhone AS P ON CLIENT_1.LoanClientID = P.LoanClientID AND CLIENT_1.PhoneTypeRef = P.PhoneTypeRef AND 
                                                   CLIENT_1.Target_LoanClientPhoneID = P.Target_LoanClientPhoneID)
    SELECT     CLIENT.LoanClientID, H.PhoneNumber AS PhoneHome, W.PhoneNumber AS PhoneWork, M.PhoneNumber AS PhoneMobile, 
                            O.PhoneNumber AS PhoneOther
INTO [dbo].[t_ClientPhones]
     FROM         dbo.Target_LoanClient AS CLIENT LEFT OUTER JOIN
                            PC AS H ON CLIENT.LoanClientID = H.LoanClientID AND H.PhoneTypeRef = ''Home'' LEFT OUTER JOIN
                            PC AS W ON CLIENT.LoanClientID = W.LoanClientID AND W.PhoneTypeRef = ''Work'' LEFT OUTER JOIN
                            PC AS M ON CLIENT.LoanClientID = M.LoanClientID AND M.PhoneTypeRef = ''Mobile'' LEFT OUTER JOIN
                            PC AS O ON CLIENT.LoanClientID = O.LoanClientID AND O.PhoneTypeRef = ''Other''

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[Target_PromiseToPay]'') AND name = N''IX_Target_PromiseToPay_AccountNumber'')BEGIN
CREATE NONCLUSTERED INDEX IX_Target_PromiseToPay_AccountNumber ON dbo.Target_PromiseToPay
	(	AccountNumber
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX IX_Target_PromiseToPay_CreatedUser ON dbo.Target_PromiseToPay
	(	CreatedUser
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''AFC-HOME\DataMining'')BEGIN
CREATE USER [AFC-HOME\DataMining] FOR LOGIN [AFC-HOME\DataMining]

EXEC sp_addrolemember N''db_owner'', N''AFC-HOME\DataMining''
END


'



